﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_Bonus:DA_Matrix
    {
        string xlsConnectStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0""";
        //string xlsConnectStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""";
        
        public DA_Bonus()
        {
            tableName = "bonus";
        }
        protected bool setTableSchema = false;

        public bool Insert(string p_misGroup,string p_cell_value
            ,string p_user_id,string p_row_pos,string p_sheetname)
        {
            if (setTableSchema == false)
            {
                setTableSchema = true;
                FillTableSchema(tableName);
            }
            whereList = new List<string>();
            fieldList = new List<field>();
            string sql = @"
INSERT INTO BONUS(misgroup,cell_value,init_time,user_id,row_pos,sheetname)
VALUES(@p_misGroup,@p_cell_value,GETDATE(),@p_user_id,@p_row_pos,@p_sheetName)
";
            
            FillFieldList(new string[] { p_misGroup }, new string[] { "@p_misGroup" }, "misgroup");
            FillFieldList(new string[] { p_cell_value }, new string[] { "@p_cell_value" }, "cell_value");
            FillFieldList(new string[] { p_user_id }, new string[] { "@p_user_id" }, "user_id");
            FillFieldList(new string[] { p_row_pos }, new string[] { "@p_row_pos" }, "row_pos");
            FillFieldList(new string[] { p_sheetname }, new string[] { "@p_sheetname" }, "sheetname");
            AddParameter(sql);
            int iResult = Common.DAL.ExecuteNoneQuery(cmd);
            return iResult < 0 ? false : true;

        }

        public DataTable GetDataBySheet(string p_sheetName)
        {
            string sql = string.Format(getSqlStatement(), p_sheetName);
            return getData(sql, p_sheetName);
        }

        /// <summary>
        /// 傳入所有SHEET ，一次撈回來
        /// </summary>
        /// <param name="p_sheetNames"></param>
        /// <returns></returns>
        public DataTable GetDataBySheet(string[] p_sheetNames)
        {
            StringBuilder sb = new StringBuilder();
            foreach (string sht in p_sheetNames)
            {
                sb.AppendFormat("!{0}",
                string.Format(getSqlStatement(), sht));
            }
            return getData(sb.ToString().Substring(1).Replace("!", " UNION ALL")
                , "Sheets");
        }

        public DataTable GetSquaredAreaByLevel(string p_level)
        {
            return getSquaredAreaByLevel("BONUS", p_level);
        }

        /// <summary>
        /// 201104-27 Bonus 這一個表格可以共用其它的excel 表格，今年的矩陣表也用bonus 這一個table 
        /// </summary>
        /// <param name="p_level"></param>
        /// <param name="p_team"></param>
        /// <returns></returns>
        public DataTable GetSquaredAreaByLevel(string p_level, string p_team)
        {
            return getSquaredAreaByLevel(p_team, p_level);
        }

        /// <summary>
        /// 傳回空table 讓資料可以一次新增進DB
        /// </summary>
        /// <returns></returns>
        public DataTable GetBonusEmptyTable()
        {
            DataTable dt = new DataTable("bonus");
            dt.Columns.Add(new DataColumn("MISGroup", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("cell_value", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("user_id", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("ROW_POS", System.Type.GetType("System.Int16")));
            dt.Columns.Add(new DataColumn("SHEETNAME", System.Type.GetType("System.String")));
            return dt;
        }

        protected string getSqlStatement()
        {
            return @"
SELECT 
    A.SHEETNAME		
    ,A.MISGroup
	,A.cell_value
	,A.ROW_POS			
	FROM(
		SELECT
			*
			,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup ORDER BY A.INIT_Time DESC) AS SEQ
		FROM bonus AS A 
        WHERE A.SHEETNAME = '{0}'
		)A
	WHERE A.SEQ = 1	";
        }

        public bool InsertFromXls(string p_xls, string p_level, string p_id
           , string p_team)
        {
            DA_Squared sq = new DA_Squared();
            return sq.InsertFromXls(p_xls, p_level, p_id
                , GetBonusEmptyTable(), p_team);
        }

        public void FillBonusData(string p_xls,string[] p_shts)
        {
            string sql, cnStr;
            cnStr = string.Format(xlsConnectStr, p_xls);
            //sql = "UPDATE [Download$a11:c600] SET CELL_VALUE = {2} WHERE SHEETNAME = '{0}' AND MISGROUP ='{1}'";
            sql = "UPDATE [DOWNLOAD$a11:c1600] SET CELL_VALUE = ? WHERE SHEETNAME = ? AND MISGROUP = ? ";
            using (System.Data.OleDb.OleDbConnection cn
                 = new System.Data.OleDb.OleDbConnection(cnStr))
            {
                cn.Open();
                //System.Data.OleDb.OleDbCommand cmd = cn.CreateCommand();                
                System.Data.OleDb.OleDbCommand oleCmd = cn.CreateCommand();
                DataTable dt = GetDataBySheet(p_shts) ;
                foreach (DataRow row in dt.Rows)
                {
                    oleCmd.CommandText = sql;
                    oleCmd.Parameters.Add("@cv", System.Data.OleDb.OleDbType.VarWChar);
                    oleCmd.Parameters.Add("@sh", System.Data.OleDb.OleDbType.VarWChar);
                    oleCmd.Parameters.Add("@grp", System.Data.OleDb.OleDbType.VarWChar);
                    oleCmd.Parameters["@cv"].Value = 
                        string.IsNullOrEmpty(row[2].ToString()) 
                        ? "0" : row[2].ToString();
                    oleCmd.Parameters["@sh"].Value = row[0].ToString();
                    oleCmd.Parameters["@grp"].Value = row[1].ToString();                    
                    oleCmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
            }
        }
    }
}
